package edu.jptest.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jptest.bean.QuestionOrigin;
import edu.jptest.util.GetDataBaseConnection;
import edu.jptest.util.UnitLesson;

public class QuestionOriginDAO {

	// 插入来源question_origin,textbook_name,unit,lesson
	public boolean insertQuestionOrigin(QuestionOrigin questionOrigin) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "insert into question_origin(textbook_name,unit,lesson) values(?,?,?)";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, questionOrigin.getTextbookName());
			pstm.setInt(2, questionOrigin.getUnit());
			pstm.setInt(3, questionOrigin.getLesson());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 删除来源
	public boolean deleteQuestionOrigin(int questionOriginId) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "delete from question_origin where question_origin_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questionOriginId);
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 通过教材名删除教材
	public boolean deleteQuestionOriginByBookName(String bookName) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "delete from question_origin where textbook_name=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, bookName);
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 更改来源
	public boolean updateQuestionOrigin(QuestionOrigin questionOrigin) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "update question_origin set textbook_name=?,unit=?,lesson=? where question_origin_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, questionOrigin.getTextbookName());
			pstm.setInt(2, questionOrigin.getUnit());
			pstm.setInt(3, questionOrigin.getLesson());
			pstm.setInt(4, questionOrigin.getQuestionOriginId());
			return pstm.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 通过来源id，查询来源对象
	public QuestionOrigin getQuestionOriginByQuestionOriginId(
			int questionorigin_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		QuestionOrigin questionOrigin = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select * from question_origin where question_origin_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questionorigin_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionOrigin.setQuestionOriginId(resultSet.getInt(1));
				questionOrigin.setTextbookName(resultSet.getString(2));
				questionOrigin.setUnit(resultSet.getInt(3));
				questionOrigin.setLesson(resultSet.getInt(4));
				return questionOrigin;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return new QuestionOrigin();
	}

	// 通过教材、单元，获得来源id
	public int getQuestionOriginIdByBookNameAndUnit(String book_name, int unit) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select question_origin_id from question_origin where textbook_name=? and unit=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			pstm.setInt(2, unit);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return 0;
	}

	// 通过教材、课别，获得来源id
	public int getQuestionOriginIdByBookNameAndLesson(String book_name,
			int lesson) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select question_origin_id from question_origin where textbook_name=? and lesson=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			pstm.setInt(2, lesson);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				return resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return 0;
	}

	// 获得所有教材
	public List<String> getAllBooks() {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<String> booksName = new ArrayList<String>();
		try {
			String sql = "select distinct textbook_name from question_origin";
			pstm = connection.prepareStatement(sql);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				booksName.add(resultSet.getString(1));
			}
			return booksName;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 获得教材单元和课别的对应关系
	public List<UnitLesson> getUnitLesson(String book_name) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<UnitLesson> unitLessonList = new ArrayList<UnitLesson>();
		try {
			String sql = "select unit, min(lesson) ,max(lesson) from  question_origin where textbook_name=? group by unit";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				UnitLesson unitLesson = new UnitLesson();
				unitLesson.setBookName(book_name);
				unitLesson.setUnit(resultSet.getInt(1));
				unitLesson.setStart(resultSet.getInt(2));
				unitLesson.setEnd(resultSet.getInt(3));
				unitLessonList.add(unitLesson);
			}
			return unitLessonList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 获得某教材中的单元数////////////////////////////////
	public int getUnitsByBook(String book_name) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		int unitsCount = 0;
		try {
			String sql = "select  max(unit) from question_origin where textbook_name=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				unitsCount = resultSet.getInt(1);
			}
			return unitsCount;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return 0;
	}

	// 获得某教材中的课数
	public int getLessonsByBook(String book_name) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		int lessonCount = 0;
		try {
			String sql = "select count(lesson) from question_origin where textbook_name=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				lessonCount = resultSet.getInt(1);
			}
			return lessonCount;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return 0;
	}

	// 获得某教材中、某单元的课数////////////////
	public int getLessonsByBookAndBookName(String book_name, int unit) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<String> contentsId = new ArrayList<String>();
		try {
			String sql = "select count(lesson) from question_origin where textbook_name=? and unit=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			pstm.setInt(2, unit);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				contentsId.add(resultSet.getString(1));
			}
			return 0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return 0;
	}

	// 获得某本教材的单元和课别列表
	public List<QuestionOrigin> getUnitLessonList(String book_name) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<QuestionOrigin> unitLessonList = new ArrayList<QuestionOrigin>();
		try {
			String sql = "select unit, lesson from question_origin where textbook_name=?";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				QuestionOrigin questionOrigin = new QuestionOrigin();
				questionOrigin.setUnit(resultSet.getInt(1));
				questionOrigin.setLesson(resultSet.getInt(2));
				unitLessonList.add(questionOrigin);
			}
			return unitLessonList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}
	
	// 通过教材、课别区间，获得来源id
	public List<Integer> getQuestionOriginIdByBookNameAndLessons(String book_name, int beginLesson,int endLesson) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		List<Integer> questionOriginIds = new ArrayList<Integer>();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select question_origin_id from question_origin where textbook_name=? and (lesson>=? and lesson<=?)";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			pstm.setInt(2, beginLesson);
			pstm.setInt(3, endLesson);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionOriginIds.add(resultSet.getInt(1));
			}
			return questionOriginIds;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

// 通过教材、单元区间，获得来源id
	public List<Integer> getQuestionOriginIdByBookNameAndUnits(String book_name, int beginUnit,int endUnit) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		List<Integer> questionOriginIds = new ArrayList<Integer>();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select question_origin_id from question_origin where textbook_name=? and (unit>=? and unit<=?)";
			pstm = connection.prepareStatement(sql);
			pstm.setString(1, book_name);
			pstm.setInt(2, beginUnit);
			pstm.setInt(3, endUnit);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionOriginIds.add(resultSet.getInt(1));
			}
			return questionOriginIds;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}
}
